package com.xishu.dao.jpa;

import com.xishu.entity.order.OrderPayTypeMapping;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface OrderPayTypeMappingDao extends JpaRepository<OrderPayTypeMapping, Long>, JpaSpecificationExecutor<OrderPayTypeMapping> {
    /**
     * 年月日选项，不带名称集合查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , pay_type as tmpId, sum(cash) as value, " +
            " pay_type_name_zh as y_zh, pay_type_name_en as y_en " +
            "from order_pay_type_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by pay_type_name_zh,pay_type_name_en,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> report(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit);

    /**
     * 年月日选项，带中文名称集合查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param payTypeName_zh
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , pay_type as tmpId, sum(cash) as value, " +
            " pay_type_name_zh as y_zh, pay_type_name_en as y_en " +
            "from order_pay_type_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and pay_type_name_zh in (:payTypeName_zh)" +
            "group by m.pay_type_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> ListIdReportZh(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit , List<String> payTypeName_zh);

    /**
     * 年月日选项，带中文名称集合查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param payTypeName_en
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , pay_type as tmpId, sum(cash) as value, " +
            " pay_type_name_zh as y_zh, pay_type_name_en as y_en " +
            "from order_pay_type_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and pay_type_name_en in (:payTypeName_en)" +
            "group by m.pay_type_name_en,FROM_UNIXTIME(create_time / 1000, :timeUnit)", nativeQuery = true)
    public List<Tuple> ListIdReportEn(List<Long> shopIdList, Long startTime, Long endTime, String timeUnit,List<String> payTypeName_en);

    /**
     *总计选项，不带名称集合查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select pay_type as tmpId, sum(cash) as value, " +
            " pay_type_name_zh as y_zh, pay_type_name_en as y_en " +
            "from order_pay_type_mapping m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by pay_type_name_zh,pay_type_name_en", nativeQuery = true)
    public List<Tuple> reportTotal(List<Long> shopIdList, Long startTime, Long endTime);

    /**
     * 总计选项，带中文名称集合查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param payTypeName_zh
     * @return
     */
    @Query(value = "select pay_type as tmpId, sum(cash) as value, " +
            " pay_type_name_zh as y_zh, pay_type_name_en as y_en " +
            "from order_pay_type_mapping m  " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and pay_type_name_zh in (:payTypeName_zh) " +
            "group by pay_type_name_zh", nativeQuery = true)
    public List<Tuple> ListIdReportTotalZh(List<Long> shopIdList, Long startTime, Long endTime,List<String> payTypeName_zh);

    /**
     * 总计选项，带英文名称集合查总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param payTypeName_en
     * @return
     */
    @Query(value = "select pay_type as tmpId, sum(cash) as value, " +
            " pay_type_name_zh as y_zh, pay_type_name_en as y_en " +
            "from order_pay_type_mapping m  " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and pay_type_name_en in (:payTypeName_en) " +
            "group by pay_type_name_en", nativeQuery = true)
    public List<Tuple> ListIdReportTotalEn(List<Long> shopIdList, Long startTime, Long endTime,List<String> payTypeName_en);


    /**
     * 计算当天的金额
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  payType as tmpId, sum(cash) as value , count(*) as count from order_pay_type_mapping m where m.shopId =?1 and m.createTime >= ?2 and m.createTime <= ?3 group by m.payType")
    public List<Tuple> count(Long shopId, Long startTime, Long endTime);

    @Query("select  sum(cash) as value , orderType as orderType , payType as payType  from order_pay_type_mapping m where m.shopId =?1 and m.createTime >= ?2 and m.createTime <= ?3 group by m.orderType,payType")
    public List<Tuple> group(Long shopId, Long startTime, Long endTime);

    @Query(value = "select pay_type_name_zh as name_zh,pay_type_name_en as name_en from order_pay_type_mapping m " +
            "where m.shop_id =:shopId group by pay_type_name_zh, pay_type_name_en", nativeQuery = true)
    public List<Tuple> shopSearchItem(Long shopId);
}
